-- ---------- ---------- ---------- ---------- ---------- ---------- ----------
-- Notes about code:
--    Intended use for removing StoreRemoteMedia files before a given
--        timestamp.
--    Could do something such as:
--        SET @the_domain := gnusocial.no/;
--        CONCAT('http://',@the_domain,'%')
--
-- Acknowledgements:
--    @moonman
--    retrieved 2018-05-23T03-36-44Z
--    https://shitposter.club/notice/9130097
--    https://hastebin.com/xamoyeqiva.sql
--
-- ---------- ---------- ---------- ---------- ---------- ---------- ----------

SET autocommit = 0;

CREATE TEMPORARY TABLE to_delete AS (
    SELECT t2.file_id
    FROM (
        SELECT t1.file_id
        FROM (
            SELECT f.id AS file_id,
                   max(n.modified) AS notice_modified
            FROM
                file f,
                notice n,
                file_to_post x
            WHERE
                LOWER(f.url) NOT LIKE 'http://gnusocial.no/%'
                AND LOWER(f.url) NOT LIKE 'https://gnusocial.no/%'
                AND f.id = x.file_id
                AND x.post_id = n.id
            GROUP BY f.id
        ) AS t1
        WHERE t1.notice_modified < '2014-01-01 00:00:00'
    ) AS t2
);

DELETE FROM file_to_post WHERE file_id IN ( SELECT file_id FROM to_delete );

DROP TABLE to_delete;

COMMIT;

-- now run php delete_orphan_files.php
